package com.zyw.boot.tool;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/*
* 根据excel文件给sql文件中字段加注释
* */
public class SqlCommentAdder {

    public static void main(String[] args) {
        try {
            // 读取Excel文件
            Map<String, String> commentMap = readExcelFile("C:\\Users\\yiwen_zheng\\Desktop\\装备翻译.xlsx");

            // 处理SQL文件
            processSqlFile("D:\\command_db\\main1.sql", "D:\\command_db\\main2.sql", commentMap);

            System.out.println("处理完成，已生成带注释的SQL文件：output.sql");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static Map<String, String> readExcelFile(String excelFilePath) throws IOException {
        Map<String, String> commentMap = new HashMap<>();
        FileInputStream file = new FileInputStream(excelFilePath);
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0); // 第一个工作表

        for (Row row : sheet) {
            Cell columnNameCell = row.getCell(0);
            Cell commentCell = row.getCell(1);

            if (columnNameCell == null || commentCell == null) continue;

            String columnName = getCellValueAsString(columnNameCell);
            String comment = getCellValueAsString(commentCell);

            if (!columnName.isEmpty() && !comment.isEmpty()) {
                commentMap.put(columnName, comment);
            }
        }

        workbook.close();
        file.close();
        return commentMap;
    }

    private static String getCellValueAsString(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                return String.valueOf((int) cell.getNumericCellValue());
            default:
                return "";
        }
    }

    private static void processSqlFile(String inputSqlPath, String outputSqlPath, Map<String, String> commentMap)
            throws IOException {
        Pattern columnPattern = Pattern.compile(
                "^[ \\t]*(`(\\w+)`|\\[(\\w+)\\]|\"(\\w+)\"|(\\w+))[ \\t]+.*",
                Pattern.CASE_INSENSITIVE);

        try (BufferedReader reader = new BufferedReader(new FileReader(inputSqlPath));
             BufferedWriter writer = new BufferedWriter(new FileWriter(outputSqlPath))) {

            String line;
            while ((line = reader.readLine()) != null) {
                Matcher matcher = columnPattern.matcher(line);
                if (matcher.find()) {
                    String columnName = null;
                    for (Integer i = 2; i <= 5; i++) {
                        if (matcher.group(i) != null) {
                            columnName = matcher.group(i);
                            break;
                        }
                    }

                    if (columnName != null && commentMap.containsKey(columnName)) {
                        String comment = commentMap.get(columnName)
                                .replace("'", "''"); // 转义单引号
                        line = addCommentToLine(line, comment);
                    }
                }
                writer.write(line);
                writer.newLine();
            }
        }
    }

    private static String addCommentToLine(String originalLine, String comment) {
        String trimmed = originalLine.trim();
        boolean endsWithComma = trimmed.endsWith(",");

        // 移除末尾逗号（如果存在）
        String withoutComma = endsWithComma ?
                originalLine.replaceAll(",\\s*$", "") :
                originalLine;

        // 添加注释
        String commented = withoutComma + " COMMENT '" + comment + "'";

        // 恢复逗号
        return endsWithComma ? commented + "," : commented;
    }
}